package com.xuhuan.demo.excel;


import com.xuhuan.demo.file.FileUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.exception.ExceptionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author huan.xu
 * @Date 2022/6/8 10:14
 * @Description excel工具类
 */
@Slf4j
public class ExcelUtil {

    /**
     * 私有构造器
     */
    private ExcelUtil() {
    }

    /**
     * 读取excel
     *
     * @param excelFile     excel文件
     * @param sheetNumber   sheet页编号，从0开始，第一个sheet页为0
     * @param contentNumber 有效数据开始的行数
     * @return
     */
    public static List<Map> readExcel(File excelFile, int sheetNumber, int contentNumber) {
        String fileType = FileUtil.getFileType(excelFile.getName());
        if (fileType.equalsIgnoreCase("xls")) {
            return readXlsExcel(excelFile, sheetNumber, contentNumber);
        } else if (fileType.equalsIgnoreCase("xlsx")) {
            return readXlsxExcel(excelFile, sheetNumber, contentNumber);
        } else {
            log.error("读取excel失败:要读取的文件不正确，不是有效的excel文件格式");
            throw new RuntimeException("读取excel失败:要读取的文件不正确，不是有效的excel文件格式");
        }

    }

    /**
     * 读取excel
     *
     * @param excelFile     excel文件
     * @param sheetNumber   sheet页编号，从0开始，第一个sheet页为0
     * @param contentNumber 有效数据开始的行数
     * @return
     */
    private static List<Map> readXlsExcel(File excelFile, int sheetNumber, int contentNumber) {
        List<Map> resultList = new ArrayList<>();
        HSSFWorkbook workbook = null;
        try {
            workbook = new HSSFWorkbook(new FileInputStream(excelFile));
            HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            int rows = sheet.getLastRowNum();
            for (int i = contentNumber - 1; i <= rows; i++) {
                HSSFRow row = sheet.getRow(i);
                Map rowMap = new HashMap(50);
                rowMap.put("row", i + 1);
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    HSSFCell cell = row.getCell(j);
                    rowMap.put("cell_" + (j + 1), getCellValue(cell));
                }
                resultList.add(rowMap);
            }
        } catch (IOException e) {
            log.error("读取excel失败：{}", ExceptionUtils.getFullStackTrace(e));
            throw new RuntimeException("要读取的文件不正确，不是有效的excel文件格式");
        }

        return resultList;
    }

    /**
     * 读取excel
     *
     * @param excelFile     excel文件
     * @param sheetNumber   sheet页编号，从0开始，第一个sheet页为0
     * @param contentNumber 有效数据开始的行数
     * @return
     */
    private static List<Map> readXlsxExcel(File excelFile, int sheetNumber, int contentNumber) {
        List<Map> resultList = new ArrayList<>();
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(new FileInputStream(excelFile));
            XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            int rows = sheet.getLastRowNum();
            for (int i = contentNumber - 1; i <= rows; i++) {
                XSSFRow row = sheet.getRow(i);
                Map rowMap = new HashMap(50);
                rowMap.put("row", i + 1);
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    XSSFCell cell = row.getCell(j);
                    rowMap.put("cell_" + (j + 1), getCellValue(cell));
                }
                resultList.add(rowMap);
            }
        } catch (IOException e) {
            log.error("读取excel失败：{}", ExceptionUtils.getFullStackTrace(e));
            throw new RuntimeException("要读取的文件不正确，不是有效的excel文件格式");
        }
        return resultList;
    }

    /**
     * 根据类型获取excel单元格的值
     *
     * @param hssfCell
     * @return
     */
    private static String getCellValue(Cell hssfCell) {
        if (hssfCell == null) {
            return "";
        }
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * 更新excel，生成新的文件
     *
     * @param sourceExcel 源excel
     * @param targetExcel 目标excel
     * @param sheetNumber sheet页编号，从0开始，第一个sheet页为0
     * @param rowNumber   行号
     * @param cellNumber  列号
     * @param cellValue   要更新的值
     */
    public static void updateExcel(File sourceExcel, File targetExcel, int sheetNumber, int rowNumber, int cellNumber, String cellValue) {
        String sourceFileType = FileUtil.getFileType(sourceExcel.getName());
        String targetFileType = FileUtil.getFileType(targetExcel.getName());
        if (!sourceFileType.equalsIgnoreCase(targetFileType)) {
            log.error("更新excel失败:要读取的文件和要生成的文件文件类型不一致");
            throw new RuntimeException("更新excel失败:要读取的文件和要生成的文件文件类型不一致");
        }
        if (sourceFileType.equalsIgnoreCase("xls")) {
            updateXlsExcel(sourceExcel, targetExcel, sheetNumber, rowNumber, cellNumber, cellValue);
        } else if (sourceFileType.equalsIgnoreCase("xlsx")) {
            updateXlsxExcel(sourceExcel, targetExcel, sheetNumber, rowNumber, cellNumber, cellValue);
        } else {
            log.error("更新excel失败:要读取的文件不正确，不是有效的excel文件格式");
            throw new RuntimeException("更新excel失败:要读取的文件不正确，不是有效的excel文件格式");
        }
    }

    /**
     * 更新excel，生成新的文件
     *
     * @param sourceExcel 源excel
     * @param targetExcel 目标excel
     * @param sheetNumber sheet页编号，从0开始，第一个sheet页为0
     * @param rowNumber   行号
     * @param cellNumber  列号
     * @param cellValue   要更新的值
     */
    private static void updateXlsExcel(File sourceExcel, File targetExcel, int sheetNumber, int rowNumber, int cellNumber, String cellValue) {
        FileInputStream file = null;
        HSSFWorkbook workbook = null;
        FileOutputStream outFile = null;

        try {
            file = new FileInputStream(sourceExcel);
            workbook = new HSSFWorkbook(file);
            HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            setXlsCellValue(sheet, rowNumber, cellNumber, "pass");
            file.close();
            outFile = new FileOutputStream(targetExcel);
            workbook.write(outFile);
            outFile.close();
        } catch (FileNotFoundException e) {
            log.error("更新excel失败：{}", ExceptionUtils.getFullStackTrace(e));
            throw new RuntimeException("更新excel失败");
        } catch (IOException e2) {
            log.error("更新excel失败：{}", ExceptionUtils.getFullStackTrace(e2));
            throw new RuntimeException("更新excel失败");
        }
    }

    /**
     * 更新excel，生成新的文件
     *
     * @param sourceExcel 源excel
     * @param targetExcel 目标excel
     * @param sheetNumber sheet页编号，从0开始，第一个sheet页为0
     * @param rowNumber   行号
     * @param cellNumber  列号
     * @param cellValue   要更新的值
     */
    private static void updateXlsxExcel(File sourceExcel, File targetExcel, int sheetNumber, int rowNumber, int cellNumber, String cellValue) {
        FileInputStream file = null;
        XSSFWorkbook workbook = null;
        FileOutputStream outFile = null;
        try {
            file = new FileInputStream(sourceExcel);
            workbook = new XSSFWorkbook(file);
            XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
            setXlsxCellValue(sheet, rowNumber, cellNumber, "pass");
            file.close();
            outFile = new FileOutputStream(targetExcel);
            workbook.write(outFile);
            outFile.close();
        } catch (FileNotFoundException e) {
            log.error("更新excel失败：{}", ExceptionUtils.getFullStackTrace(e));
            throw new RuntimeException("更新excel失败");
        } catch (IOException e2) {
            log.error("更新excel失败：{}", ExceptionUtils.getFullStackTrace(e2));
            throw new RuntimeException("更新excel失败");
        }
    }


    /**
     * 设置单元格的值
     *
     * @param sheet
     * @param rowNumber
     * @param cellNumber
     * @param value
     */
    public static void setCellValue(Sheet sheet, int rowNumber, int cellNumber, String value) {
        if (sheet instanceof HSSFSheet) {
            setXlsCellValue(sheet, rowNumber, cellNumber, value);
        } else if (sheet instanceof XSSFSheet) {
            setXlsxCellValue(sheet, rowNumber, cellNumber, value);
        }
    }

    /**
     * 设置单元格的值
     *
     * @param sheet
     * @param rowNumber
     * @param cellNumber
     * @param value
     */
    private static void setXlsCellValue(Sheet sheet, int rowNumber, int cellNumber, String value) {
        Cell cell = null;
        HSSFRow row = (HSSFRow) sheet.getRow(rowNumber);
        if (row == null) {
            row = (HSSFRow) sheet.createRow(rowNumber);
        }
        cell = row.getCell(cellNumber);
        if (cell == null) {
            cell = row.createCell(cellNumber);
        }
        cell.setCellValue(value);
    }

    /**
     * 设置单元格的值
     *
     * @param sheet
     * @param rowNumber
     * @param cellNumber
     * @param value
     */
    private static void setXlsxCellValue(Sheet sheet, int rowNumber, int cellNumber, String value) {
        Cell cell = null;
        XSSFRow row = (XSSFRow) sheet.getRow(rowNumber);
        if (row == null) {
            row = (XSSFRow) sheet.createRow(rowNumber);
        }
        cell = row.getCell(cellNumber);
        if (cell == null) {
            cell = row.createCell(cellNumber);
        }
        cell.setCellValue(value);
    }
}
